const { db, query } = require('../database/db')
// 考勤记录添加
const add = async(student_id, date, status, remarks, score) => {
    // let seachSql = `
    //     SELECT studnets.id FROM studnets
    //     WHERE students.name = ?
    // `
    // let result = await query(seachSql,[student_name])
    // const student_id = result.date.id
    let sql = `
        INSERT INTO attendance (student_id,date,status,remarks,score) VALUES (?,?,?,?,?)
    `
    return query(sql, [student_id, date, status, remarks, score])
}
// 考勤记录的删除- 软删除
const del_f = (id) => {
    let sql = `
        UPDATE attendance SET state_del = 1 WHERE id = ?
    `
    return query(sql, [id])
}
// 考勤记录的删除- 真删除
const del_t = (id) => {
    let sql = `
        DELETE FROM attendance WHERE id =?
    `
    return query(sql, [id])
}
// 考勤记录的获取数据
const get = (id) => {
    let sql = `
        SELECT 
            attendance.student_id,
            attendance.date,
            attendance.status,
            attendance.remarks,
            attendance.score,
            students.name AS student_name,
            classes.name AS class_name,
            programs.name AS program_name
        FROM 
            attendance
        JOIN 
            students ON attendance.student_id = students.id
        JOIN 
            classes ON students.class_id = classes.id
        JOIN 
            programs ON classes.program_id = programs.id
        WHERE attendance.id = ?
    `
    return query(sql, [id])
}
// 考勤记录的设置数据
const set = (id, student_id, date, status, remarks, score) => {
    let sql = `
        UPDATE attendance SET student_id=?, date=?, status=?, remarks=?, score=?
        WHERE id = ?
    `
    return query(sql, [student_id, date, status, remarks, score, id])
}
// 考勤列表搜索分页
const list = async (student_name, student_id, class_id, program_id, status, date, page, page_size) => {
    let sql = `
        SELECT 
            attendance.id,
            attendance.student_id,
            attendance.status,
            attendance.date,
            attendance.remarks,
            attendance.score,
            students.student_id AS student_id,
            students.id AS student_idOF,
            students.name AS student_name,
            classes.name AS class_name,
            programs.name AS program_name
        FROM attendance
        JOIN 
            students ON attendance.student_id = students.id
        JOIN 
            classes ON students.class_id = classes.id
        JOIN 
            programs ON classes.program_id = programs.id
    `;

    let params = [];
    let whereClauses = [`attendance.state_del = 0`];

    // 如果提供了student_name，则添加到查询条件中
    if (student_name) {
        whereClauses.push(`students.name LIKE ?`);
        params.push(`%${student_name}%`);
    }

    // 如果提供了student_id，则添加到查询条件中
    if (student_id) {
        whereClauses.push(`students.student_id = ?`);
        params.push(student_id);
    }

    // 如果提供了class_id，则添加到查询条件中
    if (class_id) {
        whereClauses.push(`classes.id = ?`);
        params.push(class_id);
    }

    // 如果提供了program_id，则添加到查询条件中
    if (program_id) {
        whereClauses.push(`programs.id = ?`);
        params.push(program_id);
    }

    // 如果提供了status，则添加到查询条件中
    if (status) {
        whereClauses.push(`attendance.status = ?`);
        params.push(status);
    }

    // 如果提供了date，则添加到查询条件中
    if (date) {
        whereClauses.push(`attendance.date = ?`);
        params.push(date);
    }

    // 如果有where条件，则添加WHERE子句
    if (whereClauses.length > 0) {
        sql += ` WHERE ${whereClauses.join(' AND ')}`;
    }

    // 添加分页逻辑
    const offset = (page - 1) * page_size; // 计算偏移量
    sql += ` GROUP BY attendance.id DESC LIMIT ?, ?`;
    params.push(offset, page_size); // 添加偏移量和每页大小到参数数组


    // 计算总数的SQL语句
    let countSql = `
        SELECT COUNT(*) as count FROM attendance 
        JOIN 
            students ON attendance.student_id = students.id
        JOIN 
            classes ON students.class_id = classes.id
        JOIN 
            programs ON classes.program_id = programs.id
        WHERE ${whereClauses.join(' AND ')}`;

    // 执行查询
    const [attendanceResult, countResult] = await Promise.all([
        query(sql, params), // 查询作业列表
        query(countSql, params) // 查询总数
    ]);
    return {
        attendance: attendanceResult, // 作业列表
        total: countResult[0].count // 总数
    };
};
// 获取所有学生
const all_student = async (class_id) => {
    let sql = `
        SELECT students.name, students.id FROM students
    `;
    const params = [];
    // 只有当 class_id 有效（非 undefined 且非空字符串）时才添加 WHERE 条件
    if (class_id !== undefined && class_id !== '') {
        sql += ' WHERE class_id = ?';
        params.push(class_id);
    }
    return query(sql, params);
};
// 
const studentIdToM = async (student_id) => {
    let sql = `
        SELECT 
            students.student_id,students.phone,students.gender,classes.name AS class_name
        FROM students 
        JOIN classes ON students.class_id = classes.id
        WHERE students.id = ?
    `
    return query(sql, [student_id])
}
module.exports = {
    add,
    del_f,
    del_t,
    get,
    set,
    list,
    all_student,
    studentIdToM
}